import numpy as np
import pandas as pd
import geopandas as gpd
import fiona
import glob
import os
import contextily as ctx
from scipy.spatial import cKDTree
from shapely.geometry import Point
import json
from tqdm.auto import tqdm
pd.set_option('min_rows', 30)
import sys
sys.path.append('..')
from importlib import reload
# import src.utils as utils
# reload(utils)
import matplotlib.pyplot as plt
plt.rcParams['figure.figsize'] = (12, 12)
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', 10)

%%time
## read in and get bounds
df = pd.read_csv("input/BCs_issued_by_AUP_TLADCs_2021FEB.csv")
bcs = gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df.X_Coordinate, df.Y_Coordinate))
# get bounds of data set (with a small buffer), useful for clipping other datasets
bcs = bcs.set_crs(epsg=4326)
bounds4326 = bcs.total_bounds
bcs = bcs.to_crs(epsg=3857)
bounds3857 =  bcs.total_bounds

CPU times: user 39.5 s, sys: 1.08 s, total: 40.6 s
Wall time: 40.5 s

## add small buffer to bounds
x_delta3857 = bounds3857[2] - bounds3857[0]
y_delta3857 = bounds3857[3] - bounds3857[1]
bounds_buffered3857 = [bounds3857[0] - y_delta3857 / 200, bounds3857[1] - x_delta3857 / 200, bounds3857[2] + y_delta3857 / 200, bounds3857[3] + x_delta3857 / 200]
bounds_buffered3857
x_delta4326 = bounds4326[2] - bounds4326[0]
y_delta4326 = bounds4326[3] - bounds4326[1]
bounds_buffered4326 = [bounds4326[0] - y_delta4326 / 200, bounds4326[1] - x_delta4326 / 200, bounds4326[2] + y_delta4326 / 200, bounds4326[3] + x_delta4326 / 200]
bounds_buffered4326

[174.15865864485, -37.2937968355, 175.54295485515, -35.877583094500004]

# one address has no ADDRESS_1, but an ADDRESS_2 with a leading digit
display(bcs[bcs.ADDRESS_1.isna() & ~bcs.ADDRESS_2.isna()][bcs[bcs.ADDRESS_1.isna() & ~bcs.ADDRESS_2.isna()].ADDRESS_2.str.contains('^[0-9]', regex=True)])
# move address 2 to address 1 for that one BC...
bcs.loc[bcs.OBS == 146813, 'ADDRESS_1'] = "25 HOLLYFORD DRV"
bcs.loc[bcs.OBS == 146813, 'ADDRESS_2'] = ""

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry
64127	146813	Sep 01, 2013 12:00:00 AM	Mar 01, 2013 12:00:00 AM	NaN	25 HOLLYFORD DRV	CLOVER PARK	Howick	New (and pre-built) house, unit, bach, crib, b...	1100	239.0	240000.0	1	Residential	Detached	NaN	NaN	1998	716014.0	2013.0	Residential - Mixed Housing Suburban Zone	Residential	18.0	MCC_MR	Main Residential	Residential	NaN	NaN	NaN	174.896997	-36.986932	POINT (19469444.625 -4437285.457)

%%time
# get number and name of street (but not 'road', 'street', 'place' etc), for matching with addresses
def number_name_bc(x):
    """extract street number and first complete word of the street name from building consents"""
    if x.ADDRESS_1 is None:
        pass
    else:
        # get number and first word of address
        joined_address = ' '.join([str(x[f'ADDRESS_{i}']) for i in [1,2, 3]]).lower()
        return ' '.join(joined_address.split(' ')[:2])

def full_address_bc(x):
    """extract full address from building consents"""
    if x.ADDRESS_1 is None:
        pass
    else:
        # get number and first word of address
        joined_address = ' '.join([str(x[f'ADDRESS_{i}']) for i in [1,2, 3] if not str(x[f'ADDRESS_{i}']) == 'nan']).lower()
        return joined_address

bcs['number_name'] = bcs.apply(number_name_bc, axis=1)
bcs['full_address'] = bcs.apply(full_address_bc, axis=1)
# to have successfully extracted the name as the second 'word': the last character is never numeric
# print(sum(bcs['number_name'].apply(lambda x: x[-1] in [i for i in range(10)])))
# there will still be some cases where there is no street number
display(bcs.number_name.sample(5))

22947        45 station
65398     22a stottholm
5621         19 ventura
149709         3 tainui
6642          111 kuaka
Name: number_name, dtype: object

CPU times: user 28.9 s, sys: 500 ms, total: 29.4 s
Wall time: 29.4 s

ax = bcs.sample(3000).plot(column='Building_Type_Group', legend=True, figsize=(20,20))
ctx.add_basemap(ax)

# split into three branches: those with an address number range (e.g. 10-20), those with a single number (e.g. 10), and those with no number
# those with no number cannot be matched with addresses to get a more accuract coordinate
bcs_non_na = bcs[~bcs.ADDRESS_1.isna()]
bcs_ranged = bcs_non_na[bcs_non_na.ADDRESS_1.str.contains('^[0-9]+-[0-9]', regex=True)]
# get all bcs which start with a digit
bcs_numbered = bcs_non_na[bcs_non_na.ADDRESS_1.str.contains('^[0-9]', regex=True)]
# exclude those in bcs_ranged
bcs_numbered = bcs_numbered[~bcs_numbered.ADDRESS_1.str.contains('^[0-9]+-[0-9]', regex=True)]
bcs_others = pd.concat([bcs_non_na[~bcs_non_na.ADDRESS_1.str.contains('^[0-9]', regex=True)], bcs[bcs.ADDRESS_1.isna()]])
print('non na:', len(bcs_non_na))
print('1. ranged:', len(bcs_ranged))
print('2. numbered:', len(bcs_numbered))
print('3. non ranged/non numbered:', len(bcs_others))
print('total bcs:', len(bcs))
print('sum of 1, 2, 3:', len(bcs_ranged) + len(bcs_numbered) + len(bcs_others))

non na: 193005
1. ranged: 6267
2. numbered: 183013
3. non ranged/non numbered: 17946
total bcs: 207226
sum of 1, 2, 3: 207226

# all ranges that have a non digit in them
rows = []
for index, row in tqdm(bcs_ranged.iterrows(), total=len(bcs_ranged)):
    r = row.number_name.split(' ')[0].split('/')[0]
    if not all([s.isdecimal() for s in r.split('-')]):
        print(row.number_name.split(' ')[0])
    rows.append(row)

1-1a
2-2a
9-9a
1-1a
3-5a
3-5a

# all ranges that have a non digit in them
rows = []
for index, row in tqdm(bcs_ranged.iterrows(), total=len(bcs_ranged)):
    r = row.number_name.split(' ')[0].split('/')[0]
    if all([s.isdecimal() for s in r.split('-')]):
        endpoints = [s for s in r.split('-')]
        assert (int(endpoints[0]) % 2) == (int(endpoints[0]) % 2)
        print((int(endpoints[0]) % 2) == (int(endpoints[0]) % 2))
    rows.append(row)

Get more accurate coordinates from NZ addresses dataset

# addressing of building consents is very inconsistent
# some building consents have street name in ADDRESS_2, some have suburb, some even have 'Auckland'
bcs[bcs.ADDRESS_2 == 'Auckland'].sample(5)

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry
24601	188214	Nov 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	528 DON BUCK ROAD	Auckland	NaN	Henderson - Massey	Non-building construction	3111	0.0	40000.0	1	Other	NaN	NaN	NaN	2014	226402.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_6	Foothills	Rural	NaN	Redhills (Fred Taylor Drive) � Stage 1	Redhills (Fred Taylor Drive) � Stage 1	174.603215	-36.824411	POINT (19436740.940 -4414660.154)
24605	188218	Nov 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	528 DON BUCK ROAD	Auckland	NaN	Henderson - Massey	Townhouses, flats, units, and other dwellings	1129	129.0	233100.0	1	Residential	Attached	NaN	NaN	2014	226402.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_6	Foothills	Rural	NaN	Redhills (Fred Taylor Drive) � Stage 1	Redhills (Fred Taylor Drive) � Stage 1	174.603215	-36.824411	POINT (19436740.940 -4414660.154)
24606	188219	Nov 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	528 DON BUCK ROAD	Auckland	NaN	Henderson - Massey	Townhouses, flats, units, and other dwellings	1129	104.0	188640.0	1	Residential	Attached	NaN	NaN	2014	226402.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_6	Foothills	Rural	NaN	Redhills (Fred Taylor Drive) � Stage 1	Redhills (Fred Taylor Drive) � Stage 1	174.603215	-36.824411	POINT (19436740.940 -4414660.154)
24614	188227	Nov 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	524-526 Don Buck Road	Auckland	NaN	Henderson - Massey	Townhouses, flats, units, and other dwellings	1129	135.0	216000.0	1	Residential	Attached	NaN	NaN	2014	226403.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_7	Living	Residential	NaN	Redhills (Fred Taylor Drive) � Stage 1	Redhills (Fred Taylor Drive) � Stage 1	174.607308	-36.824848	POINT (19437196.626 -4414720.873)
24618	188231	Nov 01, 2018 12:00:00 AM	Mar 01, 2019 12:00:00 AM	528 DON BUCK ROAD	Auckland	NaN	Henderson - Massey	Townhouses, flats, units, and other dwellings	1129	136.0	245622.0	1	Residential	Attached	NaN	NaN	2014	226402.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_6	Foothills	Rural	NaN	Redhills (Fred Taylor Drive) � Stage 1	Redhills (Fred Taylor Drive) � Stage 1	174.603215	-36.824411	POINT (19436740.940 -4414660.154)

%%time
## read in address dataset and add number_name, just like for BCs
# https://data.linz.govt.nz/layer/53353-nz-street-address/
addresses = gpd.read_file('input/lds-nz-street-address-GPKG-CLIPPED.gpkg').to_crs(3857)
def number_name_addresses(x):
    return ' '.join(x.full_address.split(' ')[:2]).lower()
addresses['number_name'] = addresses.apply(number_name_addresses, axis=1)

/data/miniconda3/envs/house-upzone/lib/python3.8/site-packages/geopandas/geodataframe.py:577: RuntimeWarning: Sequential read of iterator was interrupted. Resetting iterator. This can negatively impact the performance.
  for feature in features_lst:

CPU times: user 2min 5s, sys: 11 s, total: 2min 16s
Wall time: 2min 16s

addresses[(addresses.address_number == 9) & (addresses.full_road_name == 'Hopetoun Street')].sample(3)

	address_id	change_id	address_type	unit_value	address_number	address_number_suffix	address_number_high	water_route_name	water_name	suburb_locality	town_city	full_address_number	full_road_name	full_address	road_section_id	gd2000_xcoord	gd2000_ycoord	water_route_name_ascii	water_name_ascii	suburb_locality_ascii	town_city_ascii	full_road_name_ascii	full_address_ascii	geometry	number_name
513953	1989299	2991221	Road	202	9	None	NaN	None	None	Freemans Bay	Auckland	202/9	Hopetoun Street	202/9 Hopetoun Street, Freemans Bay, Auckland	189772	174.752374	-36.858544	None	None	Freemans Bay	Auckland	Hopetoun Street	202/9 Hopetoun Street, Freemans Bay, Auckland	POINT (19453345.297 -4419407.917)	202/9 hopetoun
513851	1915403	2991240	Road	502	9	None	NaN	None	None	Freemans Bay	Auckland	502/9	Hopetoun Street	502/9 Hopetoun Street, Freemans Bay, Auckland	189772	174.752374	-36.858544	None	None	Freemans Bay	Auckland	Hopetoun Street	502/9 Hopetoun Street, Freemans Bay, Auckland	POINT (19453345.297 -4419407.917)	502/9 hopetoun
513854	1915406	2991245	Road	603	9	None	NaN	None	None	Freemans Bay	Auckland	603/9	Hopetoun Street	603/9 Hopetoun Street, Freemans Bay, Auckland	189772	174.752374	-36.858544	None	None	Freemans Bay	Auckland	Hopetoun Street	603/9 Hopetoun Street, Freemans Bay, Auckland	POINT (19453345.297 -4419407.917)	603/9 hopetoun

addresses.sample(10)

	address_id	change_id	address_type	unit_value	address_number	address_number_suffix	address_number_high	water_route_name	water_name	suburb_locality	town_city	full_address_number	full_road_name	full_address	road_section_id	gd2000_xcoord	gd2000_ycoord	water_route_name_ascii	water_name_ascii	suburb_locality_ascii	town_city_ascii	full_road_name_ascii	full_address_ascii	geometry	number_name
52975	1002801.0	889731.0	Road	None	2.0	None	NaN	None	None	Titirangi	Auckland	2	Waerenga Place	2 Waerenga Place, Titirangi, Auckland	171349.0	174.653251	-36.934016	None	None	Titirangi	Auckland	Waerenga Place	2 Waerenga Place, Titirangi, Auckland	POINT (19442311.009 -4429913.404)	2 waerenga
158403	998738.0	886362.0	Road	None	24.0	None	NaN	None	None	Te Atatu South	Auckland	24	Tiroroa Avenue	24 Tiroroa Avenue, Te Atatu South, Auckland	191180.0	174.648857	-36.869946	None	None	Te Atatu South	Auckland	Tiroroa Avenue	24 Tiroroa Avenue, Te Atatu South, Auckland	POINT (19441821.779 -4420994.377)	24 tiroroa
596494	2227003.0	5300930.0	Road	1702	10.0	None	NaN	None	None	Auckland Central	Auckland	1702/10	Commerce Street	1702/10 Commerce Street, Auckland Central, Auc...	164395.0	174.768026	-36.845663	None	None	Auckland Central	Auckland	Commerce Street	1702/10 Commerce Street, Auckland Central, Auc...	POINT (19455087.663 -4417615.980)	1702/10 commerce
318775	1921571.0	1745258.0	Road	None	7.0	B	NaN	None	None	St Heliers	Auckland	7B	Edison Place	7B Edison Place, St Heliers, Auckland	203321.0	174.843315	-36.867704	None	None	St Heliers	Auckland	Edison Place	7B Edison Place, St Heliers, Auckland	POINT (19463468.830 -4420682.420)	7b edison
270718	1147783.0	1013962.0	Road	None	4.0	None	NaN	None	None	Devonport	Auckland	4	Huia Street	4 Huia Street, Devonport, Auckland	192089.0	174.792322	-36.830337	None	None	Devonport	Auckland	Huia Street	4 Huia Street, Devonport, Auckland	POINT (19457792.320 -4415484.318)	4 huia
124870	1878596.0	1834835.0	Road	1	95.0	None	NaN	None	None	Papatoetoe	Auckland	1/95	Motatau Road	1/95 Motatau Road, Papatoetoe, Auckland	193672.0	174.863701	-36.961238	None	None	Papatoetoe	Auckland	Motatau Road	1/95 Motatau Road, Papatoetoe, Auckland	POINT (19465738.129 -4433705.285)	1/95 motatau
94373	1487614.0	1295132.0	Road	None	18.0	None	NaN	None	None	Riverhead	Riverhead	18	Maude Street	18 Maude Street, Riverhead	205312.0	174.593912	-36.756725	None	None	Riverhead	Riverhead	Maude Street	18 Maude Street, Riverhead	POINT (19435705.379 -4405251.420)	18 maude
202231	1498243.0	1304879.0	Road	None	87.0	None	NaN	None	None	Orewa	Orewa	87	Tauranga Place	87 Tauranga Place, Orewa	204608.0	174.674711	-36.589828	None	None	Orewa	Orewa	Tauranga Place	87 Tauranga Place, Orewa	POINT (19444699.877 -4382087.267)	87 tauranga
227912	1226642.0	1082889.0	Road	None	12.0	None	NaN	None	None	Clover Park	Auckland	12	Pesaro Place	12 Pesaro Place, Clover Park, Auckland	185178.0	174.888951	-36.978107	None	None	Clover Park	Auckland	Pesaro Place	12 Pesaro Place, Clover Park, Auckland	POINT (19468548.989 -4436055.601)	12 pesaro
434079	1489551.0	1296876.0	Road	None	11.0	None	NaN	None	None	Riverhead	Riverhead	11	Great North Road	11 Great North Road, Riverhead	167386.0	174.592630	-36.759447	None	None	Riverhead	Riverhead	Great North Road	11 Great North Road, Riverhead	POINT (19435562.668 -4405629.650)	11 great

# and alternative matching would have been to take the digits + the first n characters of each address up to but not including 'road', drive', 'avenue' etc... but that would have been messy
set([x.split(' ')[-1] for x in addresses.full_road_name])

{'1',
 '12',
 '16',
 '2',
 'Access',
 'Anchorage',
 'Ara-Kotinga',
 'Avenue',
 'Bay',
 'Boardwalk',
 'Boulevard',
 'Brae',
 'Broadway',
 'Bypass',
 'Carrowmore',
 'Central',
 'Circle',
 'Circus',
 'Close',
 'Common',
 'Concourse',
 'Court',
 'Courtneys',
 'Cove',
 'Crescent',
 'Crest',
 'Dale',
 'Dell',
 'Downs',
 'Drive',
 'Earlsway',
 'East',
 'Enclave',
 'End',
 'Esplanade',
 'Extension',
 'Fairway',
 'Fen',
 'Furlong',
 'Garden',
 'Gardens',
 'Glade',
 'Glebe',
 'Glen',
 'Goldfield',
 'Green',
 'Greens',
 'Grove',
 'Gully',
 'Harbour',
 'Haven',
 'Heights',
 'Highway',
 'Hill',
 'Hilltop',
 'Ho',
 'Homestead',
 'Island',
 'Isle',
 'Kingsway',
 'Knoll',
 'Landing',
 'Lane',
 'Lea',
 'Link',
 'Lochview',
 'Loop',
 'Mall',
 'Meadway',
 'Mermaid',
 'Mews',
 'Mile',
 'Motu',
 'Neptune',
 'Nook',
 'North',
 'Oaks',
 'Oasis',
 'Oho',
 'Oval',
 'Parade',
 'Park',
 'Parkway',
 'Pass',
 'Place',
 'Plaza',
 'Point',
 'Priors',
 'Prom',
 'Promenade',
 'Quadrant',
 'Queensway',
 'Reach',
 'Retreat',
 'Ridge',
 'Rise',
 'Ritz',
 'Road',
 'Rosebowl',
 'Row',
 'Serpentine',
 'Settlement',
 'Silverfield',
 'Slope',
 'South',
 'Spa',
 'Spinney',
 'Spur',
 'Square',
 'Strand',
 'Street',
 'Tahuhu',
 'Tai',
 'Terrace',
 'Track',
 'Trail',
 'Trees',
 'Treeway',
 'Vale',
 'Valley',
 'View',
 'Views',
 'Walk',
 'Watch',
 'Waterways',
 'Way',
 'West',
 'Willowbrook',
 "d'Amarres"}

%time
addresses_tree = cKDTree(np.array(list(addresses[~addresses.geometry.isna()].geometry.apply(lambda x: (x.x, x.y)))))
bcs_numbered_tree = cKDTree(np.array(list(bcs_numbered.geometry.apply(lambda x: (x.x, x.y)))))
bcs_ranged_tree = cKDTree(np.array(list(bcs_ranged.geometry.apply(lambda x: (x.x, x.y)))))

CPU times: user 5 µs, sys: 1 µs, total: 6 µs
Wall time: 10.3 µs

# test some radii to see which is suitable
bcs_numbered_sample = bcs_numbered.sample(1000)
bcs_numbered_sample_tree = cKDTree(np.array(list(bcs_numbered_sample.geometry.apply(lambda x: (x.x, x.y)))))
matches = {}
for r in [10, 50, 100, 175] + list(range(250, 3251, 250)):
    matches[r] = []
    # list of lists: ith sub list contains indices of use_tree points within r of the ith data_tree point
    bcs_numbered_neighbours = bcs_numbered_sample_tree.query_ball_tree(addresses_tree, r)
    for i, neighbours in tqdm(enumerate(bcs_numbered_neighbours)):
        # check how many matches there are
        matches[r].append(np.sum(bcs_numbered_sample.iloc[i].number_name == addresses.iloc[neighbours].number_name))

# for r in sorted(matches.keys()):
#     print('####', r, '####')
#     print('match:', np.sum(np.array(matches[r]) == 1) / len(matches[r]))
#     print('ambiguous:', np.sum(np.array(matches[r]) > 1) / len(matches[r]))
#     print('no match:', np.sum(np.array(matches[r]) == 0) / len(matches[r]))
r_list = list(sorted(matches.keys()))
ax = plt.subplot(1,1,1)
ax.plot(r_list, [np.sum(np.array(matches[r]) == 1) / len(matches[r]) for r in r_list], label='match')
ax.plot(r_list, [np.sum(np.array(matches[r]) > 1) / len(matches[r]) for r in r_list], label='ambiguous')
ax.plot(r_list, [np.sum(np.array(matches[r]) == 0) / len(matches[r]) for r in r_list], label='no match')
ax.legend()
plt.xlabel('radius')
plt.ylabel('proportion')
plt.grid()

# test some radii to see which is suitable
def unique_radius_column_match(left, right, left_col, right_col, r=1250, left_tree=None, right_tree=None):
    """find points in right that are within r of points in left and uniquely match on a column"""
    if right_tree is None:
        right_tree = cKDTree(np.array(list(right.geometry.apply(lambda x: (x.x, x.y)))))
    if left_tree is None:
        left_tree = cKDTree(np.array(list(left.geometry.apply(lambda x: (x.x, x.y)))))
    
    # specify columns and initialize neighbour information dictionary if using the less efficient method
    # columns = ('address_id', 'full_address', 'geometry')
    # neighbour_information = {c: [] for c in columns}
    match_indices = []
    # list of lists: ith sub list contains indices of right points within r of the ith left point
    left_tree_neighbours = left_tree.query_ball_tree(addresses_tree, r)
    for i, neighbours in tqdm(enumerate(left_tree_neighbours), total=len(left_tree_neighbours)):
        # subset right to those within r distance
        right_neighbours = addresses.iloc[neighbours]
        # check how many matches there are
        match_indicator = left.iloc[i][left_col] == right_neighbours[right_col]
        if np.sum(match_indicator) == 1:
            # extract the index from right_neighbours
            # extracting the index only and later subsetting the full addresses dataframe
            # results in a ~4x speed up over extracting the requisite addresses information within this for loop
            match_indices.append(right_neighbours[match_indicator].index[0])
        elif np.sum(match_indicator) > 1:
            match_indices.append(-1)
        else:
            match_indices.append(-2)
#     print('####', r, '####')
#     print('match:', np.sum(np.array(match_indices) > -1) / len(match_indices))
#     print('ambiguous:', np.sum(np.array(match_indices) == -1) / len(match_indices))
#     print('no match:', np.sum(np.array(match_indices) == -2) / len(match_indices))
    return match_indices

# add empty row with index -1; this will be retrieved if there is no match address match
addresses.loc[-1] = addresses.loc[1]
for c in addresses.columns:
    addresses.loc[-1, c] = np.nan
    addresses.loc[-1, c]
addresses.loc[-2] = addresses.loc[-1]

bcs_numbered_sample = bcs_numbered.sample(100)

addresses.loc[match_indices].geometry.reset_index(drop=True)

0    POINT (19445145.208 -4381779.063)
1    POINT (19431050.314 -4406402.836)
2    POINT (19451295.075 -4424310.520)
3    POINT (19472792.559 -4428320.864)
4                                 None
5    POINT (19451798.838 -4402948.354)
6    POINT (19448871.690 -4388815.649)
7                                 None
8    POINT (19441001.992 -4419707.478)
9    POINT (19471097.591 -4435539.799)
Name: geometry, dtype: geometry

addresses[addresses.full_address.str.contains('23 Alma R', regex=True, na=False)]

	address_id	change_id	address_type	unit_value	address_number	address_number_suffix	address_number_high	water_route_name	water_name	suburb_locality	town_city	full_address_number	full_road_name	full_address	road_section_id	gd2000_xcoord	gd2000_ycoord	water_route_name_ascii	water_name_ascii	suburb_locality_ascii	town_city_ascii	full_road_name_ascii	full_address_ascii	geometry	number_name
65588	1122477.0	993306.0	Road	None	23.0	None	NaN	None	None	Milford	Auckland	23	Alma Road	23 Alma Road, Milford, Auckland	185138.0	174.758009	-36.775046	None	None	Milford	Auckland	Alma Road	23 Alma Road, Milford, Auckland	POINT (19453972.557 -4407797.300)	23 alma

a1 = 22882
a2 = 65588
print(addresses.loc[a1].full_address)
print(addresses.loc[a2].full_address)
geom1 = addresses.loc[a1].geometry
geom2 = addresses.loc[a2].geometry
dist = np.sqrt((geom1.x - geom2.x) **2 + (geom1.y - geom2.y) ** 2)
# 25km on google maps
dist

23 Hayward Road, Papatoetoe, Auckland
23 Alma Road, Milford, Auckland

31262.236953636555

a1 = 22582
a2 = 45588
print(addresses.loc[a1].full_address)
print(addresses.loc[a2].full_address)
geom1 = addresses.loc[a1].geometry
geom2 = addresses.loc[a2].geometry
dist = np.sqrt((geom1.x - geom2.x) **2 + (geom1.y - geom2.y) ** 2)
# 32.4km on google maps
dist

319 Boiler Gully Road, Manukau Heads
13 Station Street, Hobsonville, Auckland

40593.913696198164

a1 = 22582
a2 = 24000
print(addresses.loc[a1].full_address)
print(addresses.loc[a2].full_address)
geom1 = addresses.loc[a1].geometry
geom2 = addresses.loc[a2].geometry
dist = np.sqrt((geom1.x - geom2.x) **2 + (geom1.y - geom2.y) ** 2)
# 32.3km on google maps - more east to west than the last one
dist

319 Boiler Gully Road, Manukau Heads
139B Mill Road, Pukekohe East

46924.065595377855

a1 = 22582
a2 = 45588
print(addresses.loc[a1].full_address)
print(addresses.loc[a2].full_address)
geom1 = addresses.loc[[a1]].set_crs(3857).to_crs(2193).geometry.values[0]
geom2 = addresses.loc[[a2]].set_crs(3857).to_crs(2193).geometry.values[0]
dist = np.sqrt((geom1.x - geom2.x) **2 + (geom1.y - geom2.y) ** 2)
# 32.3km on google maps - more east to west than the last one
dist

319 Boiler Gully Road, Manukau Heads
13 Station Street, Hobsonville, Auckland

32349.647081583968

a1 = 22582
a2 = 24000
print(addresses.loc[a1].full_address)
print(addresses.loc[a2].full_address)
geom1 = addresses.loc[[a1]].set_crs(3857).to_crs(2193).geometry.values[0]
geom2 = addresses.loc[[a2]].set_crs(3857).to_crs(2193).geometry.values[0]
dist = np.sqrt((geom1.x - geom2.x) **2 + (geom1.y - geom2.y) ** 2)
# 32.3km on google maps - more east to west than the last one
dist

319 Boiler Gully Road, Manukau Heads
139B Mill Road, Pukekohe East

37431.586832019384

r = 1250
match_col = 'number_name'
match_indices = unique_radius_column_match(bcs_numbered_sample, addresses, match_col, match_col, r=r, right_tree=addresses_tree)
bcs_numbered_sample['addresses_geometry'] = addresses.loc[match_indices].geometry.reset_index(drop=True).tolist()
bcs_numbered_sample['addresses_full_address'] = addresses.loc[match_indices].full_address.reset_index(drop=True).tolist()
bcs_numbered_sample['euc_distance'] = [np.sqrt((a[0] - b[0]) ** 2 + (a[1] - b[1]) ** 2) for a, b in zip(bcs_numbered_sample.geometry.apply(lambda x: (x.x, x.y)), bcs_numbered_sample['addresses_geometry'].apply(lambda x: (x.x, x.y) if not pd.isna(x) else (0, 0)))]

bcs_numbered_sample[~bcs_numbered_sample.addresses_full_address.isna()].sort_values('euc_distance').reset_index(drop=True).euc_distance.plot()
plt.xlabel('Building Consent')
plt.ylabel('Distance to Matched Address (metres)')

Text(0, 0.5, 'Distance to Matched Address (metres)')

bcs_numbered_sample.sort_values('euc_distance')[['full_address', 'addresses_full_address', 'euc_distance']].to_csv('address_match_sample.csv')

with pd.option_context('display.max_rows', None, 'display.max_columns', None):
    display(bcs_numbered_sample.sort_values('euc_distance')[['full_address', 'addresses_full_address', 'euc_distance']])

	full_address	addresses_full_address	euc_distance
11910	302 buckland road mangere east auckland	302 Buckland Road, Mangere East, Auckland	4.597679e-04
14559	15f wonderview road leigh auckland	15F Wonderview Road, Leigh	1.381182e-03
23042	41 sunshine boulevard sunnyvale auckland	41 Sunshine Boulevard, Sunnyvale, Auckland	1.859035e-03
27621	5 cory road waiheke island auckland	5 Cory Road, Palm Beach, Waiheke Island	2.079250e-03
12352	22 aorangi place birkenhead auckland	22 Aorangi Place, Birkenhead, Auckland	2.079372e-03
27689	75 litten road cockle bay auckland	75 Litten Road, Cockle Bay, Auckland	3.735291e-03
1255	2 hakanoa street grey lynn auckland	2 Hakanoa Street, Grey Lynn, Auckland	3.822349e-03
7325	26 longfellow parade titirangi auckland	26 Longfellow Parade, Glen Eden, Auckland	3.959883e-03
5807	2 riverside drive waiuku auckland	2 Riverside Drive, Waiuku	3.961901e-03
4631	40 rimu road manurewa auckland	40 Rimu Road, Manurewa, Auckland	4.144993e-03
25134	8 paneika lane otahuhu auckland	8 Paneika Lane, Otahuhu, Auckland	4.166966e-03
1597	141 boundary road papakura auckland	141 Boundary Road, Papakura	4.245047e-03
6980	30 boocock crescent orewa auckland	30 Boocock Crescent, Orewa	4.577369e-03
25427	1 three oaks drive dairy flat auckland	1 Three Oaks Drive, Dairy Flat	5.228671e-03
22942	23 dinning road riverhead auckland	23 Dinning Road, Riverhead	5.293029e-03
26829	1 campbell road maraetai auckland	1 Campbell Road, Maraetai, Auckland	5.584889e-03
19203	88 godfrey drive silverdale auckland	88 Godfrey Drive, Orewa	5.600962e-03
89353	9 may ave te atatu south	9 May Avenue, Te Atatu South, Auckland	3.166009e+00
65938	17 arion rd takanini	17 Arion Road, Takanini	4.226894e+00
53063	20 whaley greens silverdale silverdale	20 Whaley Greens, Silverdale	5.679642e+00
179340	53 motutapu ave manly	53 Motutapu Avenue, Manly, Whangaparaoa	6.018633e+00
64497	13 wisteria lane silverdale	13 Wisteria Lane, Silverdale	6.548243e+00
147021	6 aldern rd massey	6 Aldern Road, Massey, Auckland	6.761043e+00
60541	2 conti dr flat bush	2 Conti Drive, Flat Bush, Auckland	7.048709e+00
78764	3 ruze vida dr massey	3 Ruze Vida Drive, Massey, Auckland	7.680808e+00
35010	8 bomber lane whenuapai auckland	8 Bomber Lane, Whenuapai, Auckland	8.342512e+00
149815	24 chateau rise east tamaki	24 Chateau Rise, Flat Bush, Auckland	8.492242e+00
54053	50 golden morning dr albany heights 0632	50 Golden Morning Drive, Albany Heights, Auckland	8.758135e+00
46805	52 pohutukawa parade riverhead	52 Pohutukawa Parade, Riverhead	8.919981e+00
46381	3 lagonda rise oteha	3 Lagonda Rise, Oteha, Auckland	8.994537e+00
42674	16 hakaro way takanini	16 Hakaro Way, Takanini	9.032598e+00
141546	13 bejoy rise howick	13 Bejoy Rise, East Tamaki Heights, Auckland	9.564150e+00
168598	18a newell st point chevalier	18A Newell Street, Point Chevalier, Auckland	9.880908e+00
176966	4 sonterra close manurewa	4 Sonterra Close, Randwick Park, Auckland	9.928898e+00
51613	18 hooton drive huapai huapai	18 Hooton Drive, Kumeu	9.949372e+00
165221	7 fintry pl flat bush	7 Fintry Place, Flat Bush, Auckland	9.950540e+00
67261	38 ashlynne ave papatoetoe	38 Ashlynne Avenue, Papatoetoe, Auckland	1.116769e+01
112620	108 picasso dr west harbour	108 Picasso Drive, West Harbour, Auckland	1.169479e+01
104439	105 duck creek rd stillwater	105 Duck Creek Road, Stillwater	1.181070e+01
180331	7 wharf st papakura	7 Wharf Street, Papakura	1.188761e+01
158528	90 kittiwake dr albany	90 Kittiwake Drive, Schnapper Rock, Auckland	1.270900e+01
159272	44 ploughmans ave pukekohe	44 Ploughmans Avenue, Pukekohe	1.334450e+01
35124	39 rautahi terrace redvale auckland	39 Rautahi Terrace, Redvale, Auckland	1.335194e+01
79595	43 james st mangere	43 James Street, Mangere East, Auckland	1.366246e+01
152618	50 tiriwa dr massey	50 Tiriwa Drive, Massey, Auckland	1.391903e+01
174996	3 springtide pl red beach	3 Springtide Place, Red Beach	1.421579e+01
149027	30 lynton rd mt wellington	30 Lynton Road, Mount Wellington, Auckland	1.456932e+01
194750	3 feeny cres east tamaki	3 Feeny Crescent, East Tamaki, Auckland	1.488650e+01
72140	43 landing dr albany	43 Landing Drive, Albany, Auckland	1.491017e+01
66497	96a harris street pukekohe	96A Harris Street, Pukekohe	1.577705e+01
203287	30 premila dr pukekohe	30 Premila Drive, Pukekohe	1.592811e+01
88891	8 pono pl waiuku	8 Pono Place, Waiuku	1.629278e+01
126494	4 argyle ave papakura	4 Argyle Avenue, Pahurehure, Papakura	1.632721e+01
125141	1 highwood grove torbay	1 Highwood Grove, Torbay, Auckland	1.698815e+01
29981	21 derrimore heights clover park auckland	21 Derrimore Heights, Clover Park, Auckland	1.759663e+01
163003	3 dellwood ave henderson	3 Dellwood Avenue, Henderson, Auckland	1.765939e+01
114145	5 rawson way takanini	5 Rawson Way, Takanini	1.795981e+01
94953	2/21 aorangi pl birkenhead	2/21 Aorangi Place, Birkenhead, Auckland	1.810382e+01
167225	89 mt lebanon cres manurewa	89 Mt Lebanon Crescent, The Gardens, Auckland	1.839344e+01
173149	7 drumbeg close flat bush	7 Drumbeg Close, Flat Bush, Auckland	1.915334e+01
78888	9 blanc rd silverdale	9 Blanc Road, Silverdale	1.939102e+01
106131	16 te hoe grove albany	16 Te Hoe Grove, Pinehill, Auckland	1.957382e+01
31267	91 karepiro drive stanmore bay auckland	91 Karepiro Drive, Stanmore Bay, Whangaparaoa	1.957992e+01
108386	95 atkin ave mission bay	95 Atkin Avenue, Mission Bay, Auckland	2.086714e+01
146677	26 duncansby rd stanmore bay	26 Duncansby Road, Stanmore Bay, Whangaparaoa	2.121002e+01
45224	2 gallantry crescent papakura	2 Gallantry Crescent, Papakura	2.286177e+01
154737	74 rangitoto rd papatoetoe	74 Rangitoto Road, Papatoetoe, Auckland	2.320235e+01
169823	39 hanene st st heliers	39 Hanene Street, St Heliers, Auckland	2.365556e+01
116371	57 picasso dr west harbour	57 Picasso Drive, West Harbour, Auckland	2.621310e+01
64541	50 corricvale way northcross	50 Corricvale Way, Northcross, Auckland	2.716662e+01
31281	2 valley road browns bay auckland	2 Valley Road, Browns Bay, Auckland	2.879251e+01
170825	1286 harvard lane ardmore	1286 Harvard Lane, Ardmore	2.882504e+01
185465	83 rathmar dr manurewa	83 Rathmar Drive, Manurewa, Auckland	2.903135e+01
101749	50 mountain rd mangere bridge	50 Mountain Road, Mangere Bridge, Auckland	2.911128e+01
194723	42 lady ruby dr east tamaki	42 Lady Ruby Drive, East Tamaki, Auckland	2.961019e+01
97647	8 robley cres glendowie	8 Robley Crescent, Glendowie, Auckland	3.061794e+01
123801	8 victory rd laingholm	8 Victory Road, Laingholm, Auckland	3.333315e+01
142017	74 coxhead rd manurewa	74 Coxhead Road, Manurewa, Auckland	3.386954e+01
102538	114 allum st kohimarama	114 Allum Street, Kohimarama, Auckland	4.261522e+01
179111	13a halver rd manurewa	13A Halver Road, Hillpark, Auckland	4.621618e+01
173036	70 walters rd papakura	70 Walters Road, Takanini	5.407320e+01
166356	12 mccallum dr sandspit	12 McCallum Drive, Sandspit	6.528616e+01
86995	584 blockhouse bay rd blockhouse bay	584 Blockhouse Bay Road, Blockhouse Bay, Auckland	6.552337e+01
36587	131 martyn wright road pukekohe auckland	131 Martyn Wright Road, Mauku	1.105410e+02
179618	5 georgina st freemans bay	5 Georgina Street, Freemans Bay, Auckland	1.274734e+02
138664	275 ormiston rd flat bush	275 Ormiston Road, Flat Bush, Auckland	2.069170e+02
48660	444b peach hill road drury 0	444B Peach Hill Road, Hunua	2.359865e+02
141993	77 gearon rd waiuku	77 Gearon Road, Mauku	3.009346e+02
170063	74 mangere rd otahuhu	74 Mangere Road, Otahuhu, Auckland	3.365048e+02
180116	1 otanerua rd hatfields beach	NaN	1.991127e+07
73814	531 whitmore rd matakana	NaN	1.993098e+07
167989	45 new windsor rd avondale	NaN	1.994520e+07
152383	9 clarke rd royal oak	NaN	1.995376e+07
150907	0 karioitahi rd waiuku	NaN	1.995526e+07
65901	2 o'rorke rd penrose	NaN	1.995811e+07
114640	130 bucklands beach rd bucklands beach	NaN	1.996494e+07
205755	10 watson pl	NaN	1.996506e+07
13441	303 flat bush school road flat bush auckland	NaN	1.997282e+07
93924	16 medvale ave otara	NaN	1.998620e+07
63351	149b sandhills rd great barrier island	NaN	2.001105e+07

addresses_no_na[addresses_no_na.full_address.str.contains('^[0-9]*\/303 Flat Bush', regex=True)]

	address_id	change_id	address_type	unit_value	address_number	address_number_suffix	address_number_high	water_route_name	water_name	suburb_locality	town_city	full_address_number	full_road_name	full_address	road_section_id	gd2000_xcoord	gd2000_ycoord	water_route_name_ascii	water_name_ascii	suburb_locality_ascii	town_city_ascii	full_road_name_ascii	full_address_ascii	geometry	number_name

addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^300 Flat', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[13441]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^130 Bucklands Beach', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[114640]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^8 Clarke Road, Oneh', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[167989]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

# no exact 45 - needs to have a flat
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^[0-9]*\/45 New Windsor', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[167989]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

# no 531, even with a flat
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^51. Whitmore', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[73814]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

# way off
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^1 Otanerua', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[180116]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)

def range_expand(r):
    if '/' in r:
        r, suffix = r.split('/')
        suffix = '/' + suffix
    else:
        suffix = ''
    r1, r2 = r.split('-')
    if r1.isdecimal() and r2.isdecimal():
        return [str(i) + suffix for i in range(int(r1), int(r2) + 1)]
    else:
        return r + suffix

ranged_list = []
for index, row in tqdm(bcs_ranged.iterrows(), total=len(bcs_ranged)):
    number_name = row.number_name.split(' ')
    number = number_name[0]
    name = number_name[1]
    range_expanded = range_expand(number)
    
    range_rows = [row.copy() for _ in range(len(range_expanded))]
    for i, rr in enumerate(range_rows):
        rr.number_name = range_expanded[i] + ' ' + name
    
    ranged_list.append(pd.DataFrame(range_rows))

# number of building consents that don't have NA for ADDRESS_1, and also don't have an address starting with a number
len([a for a in bcs[~bcs.ADDRESS_1.isna()].ADDRESS_1 if not a[0] in [str(i) for i in range(10)]])

3725

Join BCs with NZ Parcels

%%time
nz_parcels = gpd.read_file('input/lds-nz-primary-parcels-FGDB.zip!nz-primary-parcels.gdb')
print(nz_parcels.crs)
nz_parcels = nz_parcels.to_crs(3857)
nz_parcels = nz_parcels.cx[bounds_buffered3857[0]:bounds_buffered3857[2], bounds_buffered3857[1]:bounds_buffered3857[3]]

epsg:4167
CPU times: user 3min 58s, sys: 1.15 s, total: 3min 59s
Wall time: 3min 59s

nz_parcels = nz_parcels.cx[bounds_buffered3857[0]:bounds_buffered3857[2], bounds_buffered3857[1]:bounds_buffered3857[3]]

nz_parcels

	id	appellation	affected_surveys	parcel_intent	topology_type	statutory_actions	land_district	titles	survey_area	calc_area	geometry
0	5266447	None	None	Hydro	Primary	None	North Auckland	None	NaN	1289690.0	MULTIPOLYGON (((-12155911.950 495830.979, -121...
1	4789727	Part Lot 3 Allot 64 Section 1 SBRS OF Auckland	SO 663	DCDB	Primary	None	North Auckland	None	NaN	1.0	MULTIPOLYGON (((-12296010.220 369113.384, -122...
2	4810316	Part Tidal Lands of Manukau Harbour Survey Off...	SO 67474	DCDB	Primary	[Create] Revested in the Crown Sec 5 Foreshore...	North Auckland	None	31600000.0	31342451.0	MULTIPOLYGON (((-12349176.218 373759.767, -123...
3	4817943	Crown Land Survey Office Plan 58175	SO 58175	DCDB	Primary	[Create] Crown Land Reserved from Sale Sec 58 ...	North Auckland	None	NaN	467490.0	MULTIPOLYGON (((-12183771.380 518898.007, -121...
4	4827816	Lot 2 DP 165098	DP 165098	DCDB	Primary	None	North Auckland	NA99B/977	22979.0	22972.0	MULTIPOLYGON (((-12254746.365 394243.168, -122...
5	4937273	Lot 2 DP 38555	DP 38555	DCDB	Primary	None	North Auckland	NA7A/846	726.0	727.0	MULTIPOLYGON (((-12311163.341 359403.649, -123...
6	4991598	Lot 161 DP 6975	DP 6975	DCDB	Primary	[Create] Historic Reserve [Kawau Island Histor...	North Auckland	None	1012.0	1045.0	MULTIPOLYGON (((-12174704.920 398868.292, -121...
7	5008028	Lot 3 DP 155195	DP 155195	DCDB	Primary	None	North Auckland	NA92C/966, NA94A/279	12.0	12.0	MULTIPOLYGON (((-12306276.415 367050.209, -123...
8	5014167	Crown Land	None	DCDB	Primary	[Create] Riverbank Reserve Sec 129 Land Act 1924	North Auckland	None	NaN	181271.0	MULTIPOLYGON (((-12225342.686 475656.177, -122...
9	5030697	Lot 4 DP 61316	DP 61316	DCDB	Primary	None	North Auckland	NA18B/1061	5.0	5.0	MULTIPOLYGON (((-12303511.996 347847.490, -123...
10	5266127	None	None	Hydro	Primary	None	North Auckland	None	NaN	258258.0	MULTIPOLYGON (((-12221698.709 476727.407, -122...
11	5043516	Part Tidal Lands of Manukau Harbour Survey Off...	SO 67474	DCDB	Primary	[Create] Revested in the Crown Sec 5 Foreshore...	North Auckland	None	802300.0	778257.0	MULTIPOLYGON (((-12342742.430 390472.136, -123...
12	5074709	Crown Land Survey Office Plan 44403	SO 44403	DCDB	Primary	[Create] Crown Land Reserved from Sale (Margin...	North Auckland	None	NaN	127235.0	MULTIPOLYGON (((-12199997.005 521092.551, -121...
13	5116430	Lot 2 DP 58587	DP 58587	DCDB	Primary	None	North Auckland	NA13B/1218	13.0	12.0	MULTIPOLYGON (((-12303179.527 373018.829, -123...
14	5122300	Lot 3 DP 22134	DP 22134	DCDB	Primary	None	North Auckland	NA610/117	3.0	2.0	MULTIPOLYGON (((-12296958.755 372226.919, -122...
...	...	...	...	...	...	...	...	...	...	...	...
537275	8022000	Lot 1 DP 522526	DP 522526	Fee Simple Title	Primary	None	North Auckland	830418	234875.0	234901.0	MULTIPOLYGON (((-12309085.450 290487.154, -123...
537276	7264068	Lot 615 DP 429024	DP 429024, LT 450627	Fee Simple Title	Primary	None	North Auckland	513806	447.0	447.0	MULTIPOLYGON (((-12336992.097 315398.428, -123...
537277	7264056	Lot 51 DP 429024	DP 429024	Fee Simple Title	Primary	None	North Auckland	513825, 513826, 513827, 513828, 513829, 513830...	1772.0	1771.0	MULTIPOLYGON (((-12337109.091 315547.825, -123...
537278	7190462	Lot 548 DP 420258	DP 420258	Fee Simple Title	Primary	None	North Auckland	483135	341.0	341.0	MULTIPOLYGON (((-12336188.725 314781.089, -123...
537279	7266272	Lot 536 DP 427884	DP 427884, LT 454543	Fee Simple Title	Primary	None	North Auckland	520878	338.0	338.0	MULTIPOLYGON (((-12336035.238 314657.366, -123...
537280	7264079	Lot 626 DP 429024	DP 429024	Fee Simple Title	Primary	None	North Auckland	513817	307.0	307.0	MULTIPOLYGON (((-12336879.505 315659.652, -123...
537281	5130443	Lot 30 DP 17583	DP 17583, DP 531059	DCDB	Primary	None	North Auckland	865806, NA78B/972	813.0	813.0	MULTIPOLYGON (((-12317776.688 369234.849, -123...
537282	7535538	Lot 88 DP 469841	DP 469841	Fee Simple Title	Primary	None	North Auckland	634066	389.0	389.0	MULTIPOLYGON (((-12336254.897 315319.544, -123...
537283	7264099	Lot 646 DP 429024	DP 429024	Fee Simple Title	Primary	None	North Auckland	513837	151.0	151.0	MULTIPOLYGON (((-12336903.659 315801.323, -123...
537284	8054279	Lot 24 DP 533358	DP 533358	Fee Simple Title	Primary	None	North Auckland	877006	229.0	229.0	MULTIPOLYGON (((-12288815.890 400558.457, -122...
537285	7291940	Lot 745 DP 433546	DP 433546	Fee Simple Title	Primary	None	North Auckland	528968	247.0	247.0	MULTIPOLYGON (((-12336398.380 315153.542, -123...
537286	7266269	Lot 533 DP 427884	DP 427884, LT 454543	Fee Simple Title	Primary	None	North Auckland	520875	336.0	336.0	MULTIPOLYGON (((-12335989.801 314781.682, -123...
537287	8051540	Lot 8 DP 533517	DP 533517	Fee Simple Title	Primary	None	North Auckland	876934	185.0	185.0	MULTIPOLYGON (((-12231484.279 417503.744, -122...
537288	7264065	Lot 518 DP 429024	DP 429024	Fee Simple Title	Primary	None	North Auckland	513803	313.0	313.0	MULTIPOLYGON (((-12336358.450 315254.637, -123...
537289	7266268	Lot 532 DP 427884	DP 427884, LT 454543	Fee Simple Title	Primary	None	North Auckland	520874	331.0	331.0	MULTIPOLYGON (((-12335919.436 314757.471, -123...

537290 rows × 11 columns

join with SA2s

sa2 = gpd.read_file('NZ-SA/statistical-area-2-2020-generalised.gdb')
sa2 = sa2.to_crs(3857)

gpd.sjoin(bc_sample, sa2)

	OBS	CONSENT_DATE	MARCH_YEAR	ADDRESS_1	ADDRESS_2	ADDRESS_3	LB_Name	BUILDING_TYPE_NAME	BUILDING_TYPE_CODE	FLOOR_AREA	VALUE	BUILDINGS	Building_Type_Group	Residential_Type	Business_Group	Business_Category	Type_Class_Year	MBCODE	MBYEAR	AUP_BaseZone	AUP_BaseZone_Group	ZONE	ZONE_ID	ZONE_NAME	ZONE_TYPE	BUSINESS_TYPE	PlanAreaName	SHA_Name_154	X_Coordinate	Y_Coordinate	geometry	index_right	SA22020_V1_00	SA22020_V1_00_NAME	SA22020_V1_00_NAME_ASCII	LAND_AREA_SQ_KM	AREA_SQ_KM	SHAPE_Length
6440	219437	Aug 01, 2020 12:00:00 AM	Mar 01, 2020 12:00:00 AM	13 GRIFFEN PARK ROAD	Mount Roskill	Auckland	Puketapapa	Townhouses, flats, units, and other dwellings	1129	634.0	NaN	3	Residential	Attached	NaN	NaN	2014	587000.0	2013.0	Residential - Terrace Housing and Apartment Bu...	Residential	8.0	ACC_24	Residential 6a	Residential	NaN	NaN	NaN	174.719937	-36.920856	POINT (19449734.429 -4428080.845)	1006	139600	Lynfield North	Lynfield North	0.940925	0.940925	5537.544865
203270	65577	Mar 01, 2000 12:00:00 AM	Apr 01, 2000 12:00:00 AM		DUNN RD	DRURY	Franklin	New (and pre-built) house, unit, bach, crib, b...	1100	207.0	95000.0	1	Residential	Detached	NaN	NaN	1998	815401.0	2013.0	Road	General	27.0	NaN	NaN	NaN	NaN	NaN	NaN	175.052449	-37.151529	POINT (19486749.463 -4460249.110)	1233	166400	Ararimu	Ararimu	90.925442	90.925442	58867.417446
66156	144992	May 01, 2013 12:00:00 AM	Mar 01, 2013 12:00:00 AM	39	BEACHWOOD DR	HATFIELDS BEACH	Hibiscus and Bays	New (and pre-built) house, unit, bach, crib, b...	1100	203.0	354000.0	1	Residential	Detached	NaN	NaN	1998	170807.0	2013.0	Residential - Single House Zone	Residential	19.0	RDC_27	Residential Medium Inten	Residential	NaN	NaN	NaN	174.686288	-36.567512	POINT (19445988.651 -4378993.739)	621	112800	Hatfields Beach	Hatfields Beach	0.923252	0.923252	4314.585524
91368	39665	Jan 01, 1997 12:00:00 AM	Apr 01, 1997 12:00:00 AM	45	PALOMINO DR	WESTERN HEIGHTS	Henderson - Massey	New (and pre-built) house, unit, bach, crib, b...	1100	210.0	100000.0	1	Residential	Detached	NaN	NaN	1998	281514.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_7	Living	Residential	NaN	NaN	NaN	174.623138	-36.898135	POINT (19438958.803 -4424917.654)	908	128300	McLaren Park	McLaren Park	1.088384	1.088384	4957.562154
10567	211720	Mar 01, 2020 12:00:00 AM	Mar 01, 2020 12:00:00 AM	61 CLARK ROAD	Hobsonville	Auckland	Upper Harbour	Townhouses, flats, units, and other dwellings	1129	95.0	240000.0	1	Residential	Attached	NaN	NaN	2014	222800.0	2013.0	Residential - Mixed Housing Urban Zone	Residential	60.0	WCC_16	Special Area	Special	NaN	Scott Point, Sunderland Precinct, Hobsonville ...	Scott Point, Sunderland Precinct, Hobsonville ...	174.654705	-36.799413	POINT (19442472.770 -4411184.328)	71	120200	Hobsonville Point	Hobsonville Point	3.521568	3.521568	11979.226387